﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticleComments : ICmsArticleComments
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;

        private const string ADD_SQL = "insert into CMS_ARTICLE_COMMENTS(ARTICLE_ID,COMMENT_CONTENT,MEMBER_ID,COMMENT_TIME,STATE) values(@ARTICLE_ID,@COMMENT_CONTENT,@MEMBER_ID,getDate(),0);select @@IDENTITY";
        //添加
        public int Add(CmsArticleCommentModel model)
        {

            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@ARTICLE_ID", SqlDbType.Int,4),
                new SqlParameter("@COMMENT_CONTENT",SqlDbType.VarChar,800),
                new SqlParameter("@MEMBER_ID",SqlDbType.Int,4)
            };
            parms[0].Value = model.ArticleId;
            parms[1].Value = model.CommentContent;
            parms[2].Value = model.MemberId;
            

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();

                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }
        //查询，返回model
        private const string GET_COMMENT_SQL = "select c.ARTICLE_ID,COMMENT_CONTENT,c.MEMBER_ID,COMMENT_TIME,c.STATE,c.AUDIT_TIME,c.AUDIT_USERNAME,c.AUDIT_STATE,m.MEMBER_NICKNAME ,a.title from CMS_ARTICLE_COMMENTS c,COFCO_MEMBER_INFO m,CMS_ARTICLE a where c.MEMBER_ID=m.MEMBER_ID and c.article_id=a.ARTICLEID and COMMENT_ID=@COMMENT_ID";
        public CmsArticleCommentModel GetArticleCommentModel(int commid)
        {
            CmsArticleCommentModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@COMMENT_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = commid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_COMMENT_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new CmsArticleCommentModel();
                    mim.CommentId = commid;
                    if (!rdr.IsDBNull(0)) mim.ArticleId = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) mim.CommentContent = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) mim.MemberId = rdr.GetInt32(2);
                    if (!rdr.IsDBNull(3)) mim.CommentTime = rdr.GetDateTime(3);
                    if (!rdr.IsDBNull(4)) mim.State = rdr.GetInt32(4);
                    if (!rdr.IsDBNull(5)) mim.AuditTime = rdr.GetDateTime(5);
                    if (!rdr.IsDBNull(6)) mim.AuditUsername = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.AuditState = rdr.GetInt32(7);
                    if (!rdr.IsDBNull(8)) mim.MemberNickname = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Title = rdr.GetString(9);
                }
            }
            return mim;
        }
        //查询，返回dataset
        public DataSet GetCommentList(CmsArticleCommentModel q)
        {
            string qsql = "select t.*,u.MEMBER_NICKNAME,ca.TITLE from CMS_ARTICLE ca,CMS_ARTICLE_COMMENTS t left join COFCO_MEMBER_INFO u on t.MEMBER_ID=u.MEMBER_ID where ca.ARTICLEID=t.ARTICLE_ID ";

            if (q != null)
            {
                if (q.CommentContent != null && q.CommentContent != "")
                {
                    qsql += " and t.COMMENT_CONTENT like '%" + StringUtil.ReplaceSecStr(q.CommentContent) + "%'";
                }
                if (q.MemberId > 0)
                {
                    qsql += " and t.MEMBER_ID =" + q.MemberId;
                }
                if (q.ArticleId > 0)
                {
                    qsql += " and t.ARTICLE_ID = " + q.ArticleId;
                }
            }
            qsql += " order by t.COMMENT_TIME desc";
            
            return SQLHelper.Query(qsql);
        }
        //查询，返回list
        public IList GetCommentList(int cid)
        {
            string qsql = "select t.COMMENT_ID,t.ARTICLE_ID,t.COMMENT_CONTENT,t.MEMBER_ID,t.COMMENT_TIME,t.STATE,t.AUDIT_TIME,t.AUDIT_USERNAME,t.AUDIT_STATE,u.MEMBER_NICKNAME,u.MEMBER_IMG from CMS_ARTICLE_COMMENTS t left join COFCO_MEMBER_INFO u on t.MEMBER_ID=u.MEMBER_ID where t.state=1 ";
            if (cid != 0)
            {
                qsql += " and  t.ARTICLE_ID = " + cid.ToString() + " ";
            }
            qsql += " order by t.COMMENT_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleCommentModel camm = new CmsArticleCommentModel();

                    if (!rdr.IsDBNull(0)) camm.CommentId = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) camm.ArticleId = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) camm.CommentContent = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) camm.MemberId = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) camm.CommentTime = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(5)) camm.State = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) camm.AuditTime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) camm.AuditUsername = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) camm.AuditState = rdr.GetInt32(8);
                    if (!rdr.IsDBNull(9)) camm.MemberNickname = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) camm.Memberimg = rdr.GetString(10);

                    datalist.Add(camm);

                }
            }
            return datalist;
        }
        //审核
        public bool Audit(int commid, int auditstate, string auditusername)
        {
             SqlParameter[] parm = new SqlParameter[] {
                new SqlParameter("@AUDIT_USERNAME", SqlDbType.VarChar,50),
                
                new SqlParameter("@AUDIT_STATE",SqlDbType.Int,4)
             };
             parm[0].Value = auditusername;
             parm[1].Value = auditstate;
            int i = SQLHelper.ExecuteSql("update CMS_ARTICLE_COMMENTS set AUDIT_TIME=getDate(),AUDIT_USERNAME=@AUDIT_USERNAME,AUDIT_STATE=@AUDIT_STATE where COMMENT_ID=" + commid, parm);
            if (i > 0) return true;
            return false;
        }
        //发布
        public bool Pubsh(int commid, int state)
        {
            SqlParameter[] parm = new SqlParameter[] {
                                
                new SqlParameter("@STATE",SqlDbType.Int,4)
             };
            parm[0].Value = state;
            int i = SQLHelper.ExecuteSql("update CMS_ARTICLE_COMMENTS set STATE=@STATE where COMMENT_ID=" + commid, parm);
            if (i > 0) return true;
            return false;
        }
        //删除单行
        public bool Delete(int commid)
        {
            int i = SQLHelper.ExecuteSql("delete from CMS_ARTICLE_COMMENTS where COMMENT_ID=" + commid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }
        //删除多行
        public bool DeleteComments(string CID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_ARTICLE_COMMENTS where COMMENT_ID in (" + CID + ") ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }

    }
}
